package OrederManagement.app;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.codec.digest.DigestUtils;

import OrederManagement.pojo.Goods;
import OrederManagement.pojo.GoodsStatus;
import OrederManagement.pojo.OrderItem;
import OrederManagement.pojo.OrderStatus;
import OrederManagement.pojo.Orders;
import OrederManagement.pojo.StoreGoods;
import OrederManagement.pojo.User;
import OrederManagement.pojo.UserStatus;
import OrederManagement.util.PropertyManager;

public class ManagementDao {

	private final String driver;
	private final String url;
	private final String u;
	private final String p;

	private Connection conn = null;
	private PreparedStatement ps = null;
	private String sql;
	private ResultSet rs = null;

	public ManagementDao() {
		init();
	}

	{
		PropertyManager property = PropertyManager.getInstance();

		driver = property.getProperty("db.driver");
		url = property.getProperty("db.url");
		u = property.getProperty("db.username");
		p = property.getProperty("db.password");
	}

	private void init() {
		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(url, u, p);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public User login(String username, String password) {
		User user = null;

		sql = "select * from user where username =? and password =?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, username);
			ps.setString(2, DigestUtils.md5Hex(password));
			rs = ps.executeQuery();
			while (rs.next()) {
				user = new User();
				user.setId(rs.getInt("id"));
				user.setName(rs.getString("name"));
				user.setAddress(rs.getString("address"));
				user.setPhone(rs.getString("phone"));
				user.setUserStatusId(rs.getInt("user_status_id"));
			}
			return user;

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			clearup(null, ps, rs);
		}
		return user;
	}

	public User register(String username, String password, String name, String address, String phone) {
		sql = "insert into user (username,password,name,address,phone,user_status_id) values(?,?,?,?,?,?)";
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, username);
			ps.setString(2, DigestUtils.md5Hex(password));
			ps.setString(3, name);
			ps.setString(4, address);
			ps.setString(5, phone);
			ps.setInt(6, 2);
			boolean isTrue = ps.execute();
			if (isTrue ) {
				return login(username, password);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			clearup(null, ps, null);
		}
		return null;
	}
	public List<User> listCustomer(){
		List<User> user =new ArrayList<>();
		sql="select u.id,u.name,u.address,u.phone from user  u where u.id not in (select u.id from user u,store st where st.user_id=u.id) and u.user_status_id !=1  ";
		try {
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			while(rs.next()){
				User u =new User();
				u.setId(rs.getInt("u.id"));
				u.setName(rs.getString("u.name"));
				u.setAddress(rs.getString("u.address"));
				u.setPhone(rs.getString("u.phone"));
				user.add(u);
			}
			return user;
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			clearup(null,ps,rs);
		}
		return user;			
	}
	public List<User> listCustomer(User user){
		List<User> users =new ArrayList<>();
		sql="select u.id,u.name,u.address,u.phone from user  u where u.id not in (select u.id from user u,store st where st.user_id=u.id) and u,user_status_id !=1 and u.name =?  ";
		try {
			ps=conn.prepareStatement(sql);
			ps.setString(1, user.getName());
			rs=ps.executeQuery();
			while(rs.next()){
				User u =new User();
				u.setId(rs.getInt("u.id"));
				u.setName(rs.getString("u.name"));
				u.setAddress(rs.getString("u.address"));
				u.setPhone(rs.getString("u.phone"));
				users.add(u);
			}
			return users;
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			clearup(null,ps,rs);
		}
		return users;			
	}
	
	public List<User> listCustomer(UserStatus status){
		List<User> users =new ArrayList<>();
		sql="select u.id,u.name,u.address,u.phone  from  user  u,user_status  us where u.id not in (select u.id from user u,store st where st.user_id=u.id) "
				+ "and u,user_status_id !=1 and  us.code = ?  ";
		try {
			ps=conn.prepareStatement(sql);
			ps.setString(1, status.getCode());
			rs=ps.executeQuery();
			while(rs.next()){
				User u =new User();
				u.setId(rs.getInt("u.id"));
				u.setName(rs.getString("u.name"));
				u.setAddress(rs.getString("u.address"));
				u.setPhone(rs.getString("u.phone"));
				users.add(u);
			}
			return users;
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			clearup(null,ps,rs);
		}
		return users;			
	}
	
	

	public List<Orders> listOrders() {
		List<Orders> orders = new ArrayList<>();
		try {
			sql = "select * from orders ";
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				Orders od = new Orders();
				od.setId(rs.getInt("id"));
				od.setOrderStatusId(rs.getInt("order_status_id"));
				od.setCustomerId(rs.getInt("customer_id"));
				orders.add(od);
			}
			return orders;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			clearup(null, ps, rs);
		}
		return orders;
	}

	public List<Orders> listOrdersName(String name) {
		List<Orders> orders = new ArrayList<>();
		sql = "select o.id ,order_status_id,customer_id  from user u,orders o where  o.customer_id=u.id and u.name= ? ";
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, name);
			rs = ps.executeQuery();
			while (rs.next()) {
				Orders or = new Orders();
				or.setId(rs.getInt("o.id"));
				or.setCustomerId(rs.getInt("customer_id"));
				or.setOrderStatusId(rs.getInt("order_status_id"));
				orders.add(or);
			}
			return orders;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			clearup(null, ps, rs);
		}
		return orders;
	}

	public List<Orders> listOrders(String name) {
		List<Orders> orders = new ArrayList<>();
		sql = "select o.id,order_status_id,customer_id from orders o,order_item oi,store_goods sg,goods gd where oi.orders_id =o.id and "
				+ "oi.store_goods_id = sg.id  and  sg.goods_id=gd.id and gd.name= ?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, name);
			rs = ps.executeQuery();
			while (rs.next()) {
				Orders or = new Orders();
				or.setId(rs.getInt("o.id"));
				or.setOrderStatusId(rs.getInt("order_status_id"));
				or.setCustomerId(rs.getInt("customer_id"));
				orders.add(or);
			}
			return orders;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			clearup(null, ps, rs);
		}
		return orders;
	}

	public List<Orders> listOrders(long statusId) {
		List<Orders> orders = new ArrayList<>();
		try {
			sql = "select id ,customer_id from orders where order_status_id= ? ";
			ps = conn.prepareStatement(sql);
			ps.setLong(1, statusId);
			rs = ps.executeQuery();
			while (rs.next()) {
				Orders or = new Orders();
				or.setId(rs.getInt("id"));
				or.setCustomerId(rs.getInt("customer_id"));
				or.setOrderStatusId(rs.getInt("order_status_id"));
				orders.add(or);
			}
			return orders;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			clearup(null, ps, rs);
		}
		return orders;
	}

	public List<StoreGoods> listStoreGoods() {
		List<StoreGoods> storeGoods = new ArrayList<>();
		try {
			sql = "select sg.id,sg.price,st.name 'store_name' ,gd.type,gd.name'goods_name',gs.name'goods_status_name' "
					+ " from store_goods sg,store st,goods gd,goods_status gs where sg.store_id=st.id"
					+ " and sg.goods_id=gd.id and sg.goods_status_id =gs.id";
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				StoreGoods sg = new StoreGoods();
				sg.setId(rs.getInt("sg.id"));
				sg.setPrice(rs.getInt("sg.price"));
	            sg.setStoreName(rs.getString("store_name"));
	            sg.setType(rs.getString("gd.type"));
	            sg.setGoodsName(rs.getString("goods_name"));
	            sg.setStatusName(rs.getString("goods_status_name"));
				storeGoods.add(sg);
			}
			return storeGoods;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			clearup(null, ps, rs);
		}
		return storeGoods;

	}

	public List<Goods> listGoods() {
		List<Goods> goods = new ArrayList<>();
		try {
			sql = "select * from goods";
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				Goods gs = new Goods();
				gs.setId(rs.getInt("id"));
				gs.setType(rs.getString("type"));
				gs.setName(rs.getString("name"));
				goods.add(gs);
			}
			return goods;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			clearup(null, ps, null);
		}
		return goods;
	}
	public List<StoreGoods> listStoreGoods(String type){
		List<StoreGoods> storeGoods = new ArrayList<>();
		try {
			sql = "select sg.id,sg.price,st.name 'store_name' ,gd.type,gd.name'goods_name',gs.code "
					+ "from store_goods sg,store st,goods gd,goods_status gs where sg.store_id=st.id"
					+ "and sg.goods_id=gd.id and sg.goods_status_id =gs.id  and  gd.type=?" ;
			ps = conn.prepareStatement(sql);
			ps.setString(1, type);
			rs = ps.executeQuery();
			while(rs.next()){
				StoreGoods sg =new StoreGoods();
				sg.setId(rs.getInt("sg.id"));
				sg.setPrice(rs.getInt("sg.price"));
	            sg.setStoreName(rs.getString("store_name"));
	            sg.setType(rs.getString("gd.type"));
	            sg.setGoodsName(rs.getString("goods_name"));
	            sg.setStatusName(rs.getString("gs.name"));
			    storeGoods.add(sg);
			}
			return storeGoods;
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			clearup(null,ps,null);
		}
		return storeGoods;
	}
	public List<StoreGoods> listStoreGoods(long userId){
		List<StoreGoods> storeGoods = new ArrayList<>();
		try {
			sql = "select sg.id,sg.price,st.name 'store_name' ,gd.type,gd.name'goods_name',gs.code "
					+ "from store_goods sg,store st,goods gd,goods_status gs ,user u where sg.store_id=st.id"
					+ "and sg.goods_id=gd.id and sg.goods_status_id =gs.id  and st.user_id =u.id and u.id=?" ;
			ps = conn.prepareStatement(sql);
			ps.setLong(1, userId);
			rs = ps.executeQuery();
			while(rs.next()){
				StoreGoods sg =new StoreGoods();
				sg.setId(rs.getInt("sg.id"));
				sg.setPrice(rs.getInt("sg.price"));
				sg.setStoreName(rs.getString("store_name"));
				sg.setType(rs.getString("gd.type"));
				sg.setGoodsName(rs.getString("goods_name"));
				sg.setStatusName(rs.getString("gs.name"));
				storeGoods.add(sg);
			}
			return storeGoods;
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			clearup(null,ps,null);
		}
		return storeGoods;
	}
	public List<StoreGoods> listStoreGoodsFrom(String goodsName){
		List<StoreGoods> storeGoods = new ArrayList<>();
		try {
			sql = "select sg.id,sg.price,st.name 'store_name' ,gd.type,gd.name'goods_name',gs.code "
					+ "from store_goods sg,store st,goods gd,goods_status gs where sg.store_id=st.id"
					+ "and sg.goods_id=gd.id and sg.goods_status_id =gs.id  and  gd.name=?" ;
			ps = conn.prepareStatement(sql);
			ps.setString(1, goodsName);
			rs = ps.executeQuery();
			while(rs.next()){
				StoreGoods sg =new StoreGoods();
				sg.setId(rs.getInt("sg.id"));
				sg.setPrice(rs.getInt("sg.price"));
				sg.setStoreName(rs.getString("store_name"));
				sg.setType(rs.getString("gd.type"));
				sg.setGoodsName(rs.getString("goods_name"));
				sg.setStatusName(rs.getString("gs.name"));
				storeGoods.add(sg);
			}
			return storeGoods;
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			clearup(null,ps,null);
		}
		return storeGoods;
	}
	
	public List<StoreGoods> listStoreGoodsStatus(String goodsStatus){
		List<StoreGoods> storeGoods = new ArrayList<>();
		try {
			sql = "select sg.id,sg.price,st.name 'store_name' ,gd.type,gd.name'goods_name',gs.code "
					+ "from store_goods sg,store st,goods gd,goods_status gs where sg.store_id=st.id"
					+ "and sg.goods_id=gd.id and sg.goods_status_id =gs.id  and  gs.code=?" ;
			ps = conn.prepareStatement(sql);
			ps.setString(1, goodsStatus);
			rs = ps.executeQuery();
			while(rs.next()){
				StoreGoods sg =new StoreGoods();
				sg.setId(rs.getInt("sg.id"));
				sg.setPrice(rs.getInt("sg.price"));
				sg.setStoreName(rs.getString("store_name"));
				sg.setType(rs.getString("gd.type"));
				sg.setGoodsName(rs.getString("goods_name"));
				sg.setStatusName(rs.getString("gs.name"));
				storeGoods.add(sg);
			}
			return storeGoods;
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			clearup(null,ps,null);
		}
		return storeGoods;
	}
	public OrderStatus getStatus(String Code){
		 OrderStatus os =null;
		 sql="select id,code ,name ,description from order_status where code =?";
		 try {
			ps=conn.prepareStatement(sql);
			ps.setString(1, Code);
			rs=ps.executeQuery();
			while(rs.next()){
				long id = rs.getInt("id");
				String code =rs.getString("code");
				String name =rs.getString("name");
				String description=rs.getString("description");
				os=new OrderStatus(id,code,name,description);
			}
			return os;
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			clearup(null,ps,rs);
		}
		 return os;
		
	}
	public boolean isStoreGoods(int id){
		 sql="select 'Y' from store_goods where id =?";
		 try {
			ps=conn.prepareStatement(sql);
			ps.setLong(1, id);
			rs=ps.executeQuery();
			if(rs.next()){
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			clearup(null,ps,rs);
		}
		 return false;
		 
	}
    public long selectOrderId(String time){
    	long orderId =0;
    	sql="select id from orders where create_date_time=?";
    	try {
			ps=conn.prepareStatement(sql);
			ps.setString(1, time);
			rs=ps.executeQuery();
			if(rs.next()){
				orderId=rs.getInt("id");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
    	return orderId;
    	
    }
	public void upsertOrders(User user,String time) {

		String sqlInsert = "insert into orders (order_status_id,customer_id,create_date_time)values(?,?,?) ";


		try {
			sql = sqlInsert;
			ps = conn.prepareStatement(sql);
			ps.setLong(1, 1);
			ps.setLong(2, user.getId());
			ps.setString(3, time);

			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			clearup(null, ps, null);
		}
	}

	public void upsertOrderItem(int amount,long orderId, long storeGoodsId) {
		String sqlInsert = "insert into order_item(amount,orders_id,store_goods_id)values(?,?,?)";


		try {

			sql = sqlInsert;
			ps = conn.prepareStatement(sql);
			ps.setInt(1, amount);
			ps.setLong(2, orderId);
			ps.setLong(3, storeGoodsId);
			
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			clearup(null, ps, null);
		}
	}

	public void upsertStoreGoods(StoreGoods storeGoods) {

		String sqlInsert = "insert into store_goods(price,store_id,goods_id,goods_status_id)values(?,?,?,?)";
		String sqlUpdate = "update store_goods set price=?,store_id=?,goods_id=?,goods_status_id=?where id=?";

		boolean isUpdate = (storeGoods.getId() != -1);

		try {
			sql = sqlInsert;
			if (isUpdate) {
				sql = sqlUpdate;
			}
			ps = conn.prepareStatement(sql);
			ps.setInt(1, storeGoods.getPrice());
			ps.setLong(2, storeGoods.getStoreId());
			ps.setLong(3, storeGoods.getGoodsId());
			ps.setLong(4, storeGoods.getGoodsStatusId());
			if (isUpdate) {
				ps.setLong(5, storeGoods.getId());
			}
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			clearup(null, ps, null);
		}
	}
	public void upsertUser(User user){
		String sqlInsert ="insert into user(name,address,phone,user_status_id) values(?,?,?,?)";
		String sqlUpdate ="update user set name=?,address=?,phone =?,user_status_id=?where id=?";
		
		boolean isUpdate =(user.getId()!=-1);
		try {
			sql=sqlInsert;
			if(isUpdate){
				sql=sqlUpdate;
			}
			ps=conn.prepareStatement(sql);
			ps.setString(1, user.getName());
			ps.setString(2, user.getAddress());
			ps.setString(3, user.getPhone());
			ps.setLong(4, user.getUserStatusId());
			if(isUpdate){
				ps.setLong(5, user.getId());
			}
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			clearup(null,ps,null);
		}
		
	}
	public boolean isUsernameExists(String username) {
		sql = "select 'Y' from user where username= ?";

		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, username);
			rs = ps.executeQuery();
			if (rs.next()) {
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} finally {
			clearup(null, ps, rs);
		}

		return false;
	}


	public static void clearup(Connection connection, Statement sta, ResultSet rs) {
		try {
			if (connection != null) {
				connection.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			if (sta != null) {
				sta.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			if (rs != null) {
				rs.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	

}
